<?php 
$req_path="../";
include($req_path."includes/ini.php");
require_once($req_path."includes/db.php");

class M_Service extends DB{
    private $rowCount=0;
    public $table_name="";
	
    function M_Service(){
		parent::__construct();
	
    }
	function MonthThai2($m){
	 $thaimonth=array("ม.ค.","ก.พ.","มี.ค.","เม.ย.","พ.ค."," มิ.ย.","ก.ค.","ส.ค.","ก.ย.","ต.ค.","พ.ย.","ธ.ค.");
	  
	 return $thaimonth[$m];

	}     
    function loadData($year=0,$term=0,$class=0,$room=0,$code='',$name=''){
        
        $sqlscript= " SELECT st.code, st.student_id, info.thai_name, info.thai_surname,
						CASE WHEN info.sex ='M' THEN 'ด.ช. ' ELSE 'ด.ญ. ' END AS title_name
					, ifnull(info.thai_nickname,'') as thai_nickname
					,  sy.class_id, sy.room, cl.name  as class_name
					, (SELECT COUNT(*) FROM  tb_student_course  s1 WHERE  s1.year = sy.year and is_cancel=0 and s1.student_id = st.student_id ) course
					, (SELECT COUNT(*) FROM  tb_student_bus  s1 WHERE  s1.year = sy.year and is_cancel=0 and s1.student_id = st.student_id ) bus
					, IFNULL((	SELECT DATE_FORMAT(s1.createdate,'%d/%m/%Y')  createdate
						FROM  tb_student_course  s1 
						WHERE  s1.year = sy.year
						 and is_cancel=0 and s1.student_id =  st.student_id  
						union 
						SELECT DATE_FORMAT(s1.createdate,'%d/%m/%Y')  createdate
						FROM  tb_student_bus  s1 
						WHERE  s1.year =sy.year
						and is_cancel=0
						and s1.student_id =  st.student_id
						order by createdate desc
						limit 0,1),'') as create_date
					FROM tb_student st
					JOIN tb_student_info info on  info.student_id = st.student_id
					JOIN tb_student_year sy on sy.student_id = st.student_id
					LEFT JOIN tb_class_level cl on cl.id = sy.class_id
					WHERE st.status = 3 and sy.year=".$year;
		if ($class!=0){
			$sqlscript .=" and sy.class_id = ".$class;
		}
		if ($room!=''){
			$sqlscript .=" and sy.room = ".$room;
		}
		if ($code!=''){
			$sqlscript .=" and st.code = '".$code."'";
		}
		if ($name!=''){
			$sqlscript .=" and info.thai_name like '%".$name."%'";
		}
        $sqlscript.=" ORDER BY info.thai_name ";
		
		//print_r($sqlscript);
        $result = $this->ExecuteData($sqlscript);
        $this->rowCount = $result->RecordCount();
        return $result;
    }
	function getCourseById($id){
			//$ar=array();
			$sql = " SELECT is_pay, course
			FROM tb_student_course 
			WHERE  student_id=".$id;
			$result = $this->ExecuteData($sql);
			$this->rowCount = $result->RecordCount();
			if ($this->rowCount > 0){
				while($row = $result->FetchRow()){
					$ar[$row['course']]=$row['is_pay'];
				}
			}
			return $ar;	
	}	
	function delete_course($id){
	
		$sql = " DELETE FROM tb_student_course WHERE student_id=".$id." and is_pay=0 and is_cancel=0";
		$this->ExecuteData($sql);

	}
	function delete_course_main($id,$year,$term){
		$sql =" DELETE FROM tb_student_payment_term WHERE student_id =".$id." and fee_type=6 and isClose=0  and year=".$year." and term=".$term;
		$this->ExecuteData($sql);
	}
		function delete_car_main($id,$year,$term){
		$sql =" DELETE FROM tb_student_payment_term WHERE student_id =".$id." and fee_type=7 and isClose=0  and year=".$year." and term=".$term;
		$this->ExecuteData($sql);
	}
	function getbusById($id){
		//$ar=array();
		$sql = " SELECT   bus , is_pay
		FROM tb_student_bus
		WHERE student_id=".$id;
		$result = $this->ExecuteData($sql);
		$this->rowCount = $result->RecordCount();
		if ($this->rowCount > 0){
			while($row = $result->FetchRow()){
				$ar[$row['bus']]=$row['is_pay'];
			}
		}
		return $ar;	
	}
	function load_course($year,$term){
		$sql = " SELECT detail,cost, CASE WHEN type=1 THEN 'เดือน' 
							CASE WHEN type=4 THEN 'เทอม' 
							WHEN type=2 THEN 'วัน'
							WHEN type=3 THEN 'ชม' END cost_type	,id
				FROM tb_course 
				WHERE status='Y' year=".$year." and term=".$term;
		$result = $this->ExecuteData($sql);
        $this->rowCount = $result->RecordCount();
        return $result;
	}
	function load_course_detail($id){
		$sql = " SELECT study_date, study_time
		FROM tb_course_detail
		where course_id =".$id;
		
		$result = $this->ExecuteData($sql);
        $this->rowCount = $result->RecordCount();
        return $result;
	}
	function AddCourse($id,$list,$user){
		
		$this->delete_course($id);	
		
		$sql = "INSERT INTO tb_student_course(year, term, student_id, course ,createdate, createby)
				SELECT  c.year , c.term, ".$id .", c.id, now(), ".$user."
				FROM tb_course  c
				LEFT JOIN tb_student_course  sc  on sc.course = c.id and student_id =".$id." and sc.is_cancel=0
				WHERE c.id in (".$list.") and c.status='Y' and student_id is null ";

		$this->ExecuteData($sql);	

		$Year = $this->getCurrentYear();
		$Term = $this->getCurrentTerm();
		
		
		$this->delete_course_main($id, $Year,$Term );
		
		/*$sql = "SELECT  c.year , c.term, cost as cost 
				FROM tb_course  c
				JOIN tb_student_course  sc  on sc.course = c.id  
				WHERE sc.student_id = ".$id. " and sc.is_cancel=0 and sc.is_pay=0 and c.year = ".$Year." and c.term=".$Term." ";
		*/
		$sql_course = " SELECT id, cost , type, month_from ,month_to FROM tb_course WHERE id in (".$list.") ";
		$result_course = $this->ExecuteData($sql_course);	
		while($row = $result_course->FetchRow()){
			if ($row['type']==1 || $row['type']==5){
				$month_from = $row['month_from'];
				$month_to = $row['month_to'];
				
				while ($month_from <= $month_to ){
					$sql = " INSERT INTO tb_student_payment_term (receipt_code, year, term, student_id, fee_type ,temp_id, month, amount, pay_amount, isClose)  ";
					$sql .= " SELECT '', ".$Year." , ".$Term." ,".$id.",6 , c.id, ".$month_from."  , c.cost, 0 , 0
						 FROM tb_course c
						 where c.id =".$row['id']." and c.status='Y' "; 
					
					$month_from+=1;
					print_r($sql);
					print_r("<br/>");
					$this->ExecuteData($sql);
				}
				
			}else{
				$sql = " INSERT INTO tb_student_payment_term (receipt_code, year, term, student_id, fee_type ,temp_id, amount, pay_amount, isClose)  ";
				$sql .= " SELECT '', ".$Year." , ".$Term." ,".$id.",6 , c.id, c.cost, 0 , 0
						 FROM tb_course c
						 LEFT JOIN tb_student_payment_term p on c.id = p.temp_id and fee_type = 6  and student_id = ".$id." and p.year=".$Year." and p.term=".$Term." 
						 where c.id =".$row['id']." and c.status='Y' and p.student_id is null ";
				//print_r($sql);
				$this->ExecuteData($sql);
			}
			
		}
		
		/*$sql = " INSERT INTO tb_student_payment_term (receipt_code, year, term, student_id, fee_type ,temp_id, amount, pay_amount, isClose)  ";
		$sql .= " SELECT '', ".$Year." , ".$Term." ,".$id.",6 , c.id, c.cost, 0 , 0
				 FROM tb_course c
				 LEFT JOIN tb_student_payment_term p on c.id = p.temp_id and fee_type = 6  and student_id = ".$id." and p.year=".$Year." and p.term=".$Term." 
				 where c.id in (".$list.") and c.status='Y' and p.student_id is null ";*/
		//$rowcnt = $result->RecordCount();
		
		/*
		if ($rowcnt > 0){
			while($total = $result->FetchRow()){
				$sql = "SELECT * FROM tb_student_payment_term WHERE student_id=".$id." and fee_type=6 and isClose=0  and year=".$Year." and term=".$Term;
				$result=$this->ExecuteData($sql);
				if ($result->RecordCount() > 0){ //Update
					$sql_update = " UPDATE tb_student_payment_term SET amount=".$total['totalCourse']." WHERE student_id=".$id." and fee_type=6  and year=".$Year." and term=".$Term;
					$this->ExecuteData($sql_update);
				} else { //Add
					$sql_add = " INSERT INTO tb_student_payment_term (receipt_code, year, term, student_id, fee_type, amount, pay_amount, isClose) 
								values( '',".$Year." , ".$Term.",".$id.",6,".$total['totalCourse'].",0,0)" ;
					$this->ExecuteData($sql_add);
				}
			}
		}*/
		return 1;
	}
	
	function AddBus($id,$list,$user){
		$Year = $this->getCurrentYear();
		$Term = $this->getCurrentTerm();
		
		$sql = " DELETE FROM tb_student_bus WHERE student_id = ".$id." and is_pay=0 and is_cancel=0 and year=".$Year." and term=".$Term;
		$this->ExecuteData($sql);	
		
		$sql = "INSERT INTO tb_student_bus(year,term,student_id, bus ,createdate, createby)
				SELECT  c.year ,c.term , ".$id." , c.id , now() , ".$user."
				FROM tb_car_manage  c
				LEFT JOIN tb_student_bus  sc  on sc.bus = c.id and sc.student_id = ".$id." and sc.is_cancel=0
				WHERE c.id in (".$list.")  and sc.student_id is null and c.year=".$Year." and c.term=".$Term;
		$this->ExecuteData($sql);		
	
		$this->delete_car_main($id,$Year,$Term);
		$sql=" INSERT INTO tb_student_payment_term (receipt_code, year, term, student_id, fee_type,temp_id, amount, pay_amount, isClose)  ";
		$sql .= " SELECT '', ".$Year." , ".$Term." ,".$id.",7, c.id, c.cost, 0 , 0
				 FROM tb_car_manage c
				 LEFT JOIN tb_student_payment_term p on c.id = p.temp_id and fee_type = 7  and student_id = ".$id." and p.year=".$Year." and p.term=".$Term." 
				 where c.id in (".$list.") and c.status='Y' and p.student_id is null ";
		/*$sql = " SELECT  c.year , c.term, sum(cost) as totalbus 
			FROM tb_car_manage  c
			JOIN tb_student_bus  sc  on sc.bus = c.id  
			WHERE sc.student_id = ".$id. " and sc.is_cancel=0 and sc.is_pay=0 and c.year = ".$Year." and c.term=".$Term."
			Group by c.year, c.term ";*/
				
		$result=$this->ExecuteData($sql);
		//$total = $result->FetchRow();
		
		/*if ($total['totalbus'] > 0){
		
			$sql = "SELECT * FROM tb_student_payment_term WHERE student_id=".$id." and fee_type=7  and isClose=0  and year=".$Year." and term=".$Term;
			///print_r($sql);
			$result=$this->ExecuteData($sql);
			if ($result->RecordCount() > 0){ //Update
				$sql_update = " UPDATE tb_student_payment_term SET amount=".$total['totalbus']." WHERE student_id=".$id." and fee_type=7 and isClose =0 and year=".$Year." and term=".$Term;
				$this->ExecuteData($sql_update);
			} else { //Add
				$sql_add = " INSERT INTO tb_student_payment_term (receipt_code, year, term, student_id, fee_type, amount, pay_amount, isClose) 
							values( '',".$Year." , ".$Term.",".$id.",7,".$total['totalbus'].",0,0)" ;
				$this->ExecuteData($sql_add);
			}
		
		}*/
		return 1;
	}
	function updateCourseTeacher($id,$cid,$tid,$Year,$Term){
	
		$sql = " UPDATE tb_student_course SET teacher = ".$tid." where student_id=".$id." and course=".$cid." and year=".$Year." and term=".$Term;
		$this->ExecuteData($sql);
		
	}
	function AbortCourse($id,$Year,$Term){
		$sql = " DELETE FROM tb_student_course WHERE student_id = ".$id." and is_pay=0 and year=".$Year." and term=".$Term;
		 	$this->ExecuteData($sql);
			
		 $sql = " DELETE FROM tb_student_payment_term WHERE fee_type=6 and student_id=".$id." and isClose=0  and year=".$Year." and term=".$Term;
		 	$this->ExecuteData($sql);
		return 1;
	
	}
	function AbortRegisterBus($id,$Year,$Term){
		 $sql = " DELETE FROM tb_student_bus WHERE student_id = ".$id." and is_pay=0 and year=".$Year." and term=".$Term;
		 	$this->ExecuteData($sql);
			
		 $sql = " DELETE FROM tb_student_payment_term WHERE fee_type=7 and student_id=".$id." and isClose=0  and year=".$Year." and term=".$Term;
		 	$this->ExecuteData($sql);
		return 1;
	}
	function AddCourse2($id,$list,$user){
		
		$this->ExecuteData("DELETE FROM tb_student_course WHERE student_id=".$id."");	
		
		$sql = " INSERT INTO tb_student_course(year, term, student_id, course,createdate, createby)
				SELECT  c.year , c.term, ".$id .", c.id, now(), ".$user."
				FROM tb_course  c
				WHERE c.id in (".$list.")   ";

		$this->ExecuteData($sql);		
	}
	
	function AddBus2($id,$list,$user){
		$sql = " DELETE FROM tb_student_bus WHERE student_id = ".$id;
		
		$this->ExecuteData($sql);	
		
		$sql = " INSERT INTO tb_student_bus(year,term,student_id, bus ,createdate, createby)
				SELECT  c.year ,c.term , ".$id." , c.car_id , now() , ".$user."
				FROM tb_car_manage  c
				WHERE c.id in (".$list.")  ";
		$this->ExecuteData($sql);		
	}
	
	function RemoveBus($id){
		$sql = " DELETE FROM tb_student_bus WHERE student_id = ".$id;
		$this->ExecuteData($sql);	
	}
    function getRowCount(){
        
        return $this->rowCount;
        
    }	
    function insertData($val){
            if (isset($val)){
				$sqlInsert=$this->InsertSqlScript("SELECT name,status , createdate, createby FROM ". $this->table_name." where 1=2 ", $val);
				$this->ExecuteData($sqlInsert);
            }
    }
	 
    function updateData($id,$val){
            if(isset($val)){
				$sqlUpdate=$this->UpdateSqlScript("SELECT name,status , updatedate, updateby FROM ". $this->table_name." where id='".$id."'" ,$val);
				 $this->ExecuteData($sqlUpdate);
				// return $sqlUpdate;
            }
    } 
   
	 
}
?>
